CSE 180 Questions And Answers With Verified Solutions
What are the application programming approaches? ANS 1. code in a specialized stored procedure
language is stored in the database, and its executed in the database when the procedure is called
2. SQL statements are embedded in a host language (ex. C)
3. Connection tools/libraries are used to allow a conventional language to access a database
4. RESTful Web Services using HTTP
What are stored procedures? ANS PSM (Persistent Stored Modules): allows us to store procedures as
database schema elements
mixture of conventional programming languages constructs and SQL
what are the alternatives to PSM? ANS - Oracle: PL/SQL (allows you to create and store
procedures/functions and can be run from Oracles generic query interface)
- PostgreSQL: PL/pgSQL (can be executed from psql
- IBM DB2: SQL PL
- MS SQL Server and Sybase: Transact-SQL (T-SQL)
What is the basic PL/pgSQL form? ANS CREATE PROCEDURE <name> (<parameter list>)
AS $$
<optional_local_declarations>
$$ LANGUAGE plpgsql;
CREATE FUNCTION <name> (<parameter list>) RETURNS <type>
AS $$
, <optional_local_declarations>
<body>
$$ LANGUAGE plpgsql;
What are the parameter modes? ANS mode-name-type triples:
- IN mode: value can be used, and value can not be changed (default)
- OUT mode: value can not be used, and value can be changed
- INOUT mode: value can be used and value can be changed
Function parameters may have any of these three modes
Procedure parameters can have mode IN or mode INOUT; they can not have mode OUT
What are the terms for invoking stored procedures and stored function? ANS a stored function may be
used in SQL expressions wherever a value of its return type could appear
PostgreSQL Stored Procedures can explicitly execute transactions, but stored functions can't
What are the kinds of PL/pgSQL statements? ANS - RETURN;
- RETURN <expression>;
- DECLARE <name> <type> [:= <initial value>];
- DECLARE
- BEGIN...END
Assignment statements:
- <variable> := <expression>;
Statement labels: you can give a statement a label by prefixing a name and colon.
What are the application programming approaches? ANS 1. code in a specialized stored procedure
language is stored in the database, and its executed in the database when the procedure is called
2. SQL statements are embedded in a host language (ex. C)
3. Connection tools/libraries are used to allow a conventional language to access a database
4. RESTful Web Services using HTTP
What are stored procedures? ANS PSM (Persistent Stored Modules): allows us to store procedures as
database schema elements
mixture of conventional programming languages constructs and SQL
what are the alternatives to PSM? ANS - Oracle: PL/SQL (allows you to create and store
procedures/functions and can be run from Oracles generic query interface)
- PostgreSQL: PL/pgSQL (can be executed from psql
- IBM DB2: SQL PL
- MS SQL Server and Sybase: Transact-SQL (T-SQL)
What is the basic PL/pgSQL form? ANS CREATE PROCEDURE <name> (<parameter list>)
AS $$
<optional_local_declarations>
$$ LANGUAGE plpgsql;
CREATE FUNCTION <name> (<parameter list>) RETURNS <type>
AS $$
, <optional_local_declarations>
<body>
$$ LANGUAGE plpgsql;
What are the parameter modes? ANS mode-name-type triples:
- IN mode: value can be used, and value can not be changed (default)
- OUT mode: value can not be used, and value can be changed
- INOUT mode: value can be used and value can be changed
Function parameters may have any of these three modes
Procedure parameters can have mode IN or mode INOUT; they can not have mode OUT
What are the terms for invoking stored procedures and stored function? ANS a stored function may be
used in SQL expressions wherever a value of its return type could appear
PostgreSQL Stored Procedures can explicitly execute transactions, but stored functions can't
What are the kinds of PL/pgSQL statements? ANS - RETURN;
- RETURN <expression>;
- DECLARE <name> <type> [:= <initial value>];
- DECLARE
- BEGIN...END
Assignment statements:
- <variable> := <expression>;
Statement labels: you can give a statement a label by prefixing a name and colon.
