存储过程，给个例子

存储过程在 sql server 里面创建
自己参考其帮助
在dephi 里通过 adostoreproc 调用执行最简单
参考 dephi 帮助

ORACLE:
CREATE PROCEDURE TESTINSERT (ID IN NUMBER,NAME IN VARCHAR2,FLAG OUT NUMBER)
IS
BEGIN
   INSERT INTO MYTAB VALUES( ID,NAME);
   COMMIT;
   FLAG:=1;
EXCEPTION
   WHEN OTHERS THEN
      FLAG:=0;
END TESTINSERT;DELPHI的调用：
   添加一个storedproc的控件，设置storedprocname为testinsert,以及database
   执行：
   storedproc1.params[0].value:=1;
   storedproc1.params[1].value:='a';
   storedproc1.prepare;
   storedproc1.execproc;
   if storedproc1.params[2].value=0 then showmessage('失败‘）
      else showmessage('成功’);

各位大虾：
   执行的我知道了。
   那哪些创建存储过程应写在哪个地方呢？
   全部都是在delphi做，当然包括创建.

我认为应在服务器端创建
动态创建应用query1
使用sql 语句

A.    Use a simple procedure with a complex SELECTThis stored procedure returns all authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure does not use any parameters.USE pubsIF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
  The au_info_all stored procedure can be executed in these ways:EXECUTE au_info_all-- OrEXEC au_info_all  Or, if this procedure is the first statement within the batch:au_info_all  B.    Use a simple procedure with parameters This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure accepts exact matches for the parameters passed.USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE  au_fname = @firstname
AND au_lname = @lastname
GO
  The au_info stored procedure can be executed in these ways:EXECUTE au_info 'Dull', 'Ann'-- OrEXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'-- OrEXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'-- OrEXEC au_info 'Dull', 'Ann'-- OrEXEC au_info @lastname = 'Dull', @firstname = 'Ann'-- OrEXEC au_info @firstname = 'Ann', @lastname = 'Dull'  Or, if this procedure is the first statement within the batch:au_info 'Dull', 'Ann'-- Orau_info @lastname = 'Dull', @firstname = 'Ann'-- Orau_info @firstname = 'Ann', @lastname = 'Dull'  C.    Use a simple procedure with wildcard parameters This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure pattern matches the parameters passed or, if not supplied, uses the preset defaults.USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
  The au_info2 stored procedure can be executed in many combinations. Only a few combinations are shown here:EXECUTE au_info2-- OrEXECUTE au_info2 'Wh%'-- OrEXECUTE au_info2 @firstname = 'A%'-- OrEXECUTE au_info2 '[CK]ars[OE]n'-- OrEXECUTE au_info2 'Hunter', 'Sheryl'-- OrEXECUTE au_info2 'H%', 'S%'  D.    Use OUTPUT parametersOUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQL statements to access a value set during the procedure execution. In this example, a stored procedure (titles_sum) is created and allows one optional input parameter and one output parameter.
First, create the procedure:USE pubsGOIF EXISTS(SELECT name FROM sysobjectsWHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO
  Next, use the OUTPUT parameter with control-of-flow language. Note  The OUTPUT variable must be defined during the table creation as well as during use of the variable.The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @@SUM = variable is used). DECLARE @@TOTALCOST moneyEXECUTE titles_sum 'The%', @@TOTALCOST OUTPUTIF @@TOTALCOST < 200 BEGINPRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
  Here is the result set:Title Name                                                                       ------------------------------------------------------------------------ The Busy Executive's Database GuideThe Gourmet MicrowaveThe Psychology of Computer Cooking  (3 row(s) affected)  Warning, null value eliminated from aggregate. All of these titles can be purchased for less than $200.  E.    Use an OUTPUT cursor parameterOUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger.
First, create the procedure that declares and then opens a cursor on the titles table:USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles

OPEN @titles_cursor
GO
  Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.USE pubsGODECLARE @MyCursor CURSOREXEC titles_cursor @titles_cursor = @MyCursor OUTPUTWHILE (@@FETCH_STATUS = 0)BEGINFETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
  F.    Use the WITH RECOMPILE optionThe WITH RECOMPILE clause is helpful when the parameters supplied to the procedure will not be typical and when a new execution plan should not be cached or stored in memory.USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = 'titles_by_author' AND type = 'P')
DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',
title AS Title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO
  G.    Use the WITH ENCRYPTION optionThe WITH ENCRYPTION clause hides the text of a stored procedure from users. This example creates an encrypted procedure and uses the sp_helptext system stored procedure and then attempts to select directly from the syscomments table.IF EXISTS (SELECT name FROM sysobjectsWHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO

EXEC sp_helptext encrypt_this
  Here is the result set from the encrypt_this stored procedure:The object's comments have been encrypted.  Next, select the identification number and text of the encrypted stored procedure contents.SELECT c.id, c.text FROM syscomments c INNER JOIN sysobjects oON c.id = o.id
WHERE o.name = 'encrypt_this'
  Here is the result set:Note  The text column output is shown on a separate line. When executed, this information appears on the same line as the id column information.id         text                                                                                                                                                                                                                                                            ---------- ------------------------------------------------------------1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????  (1 row(s) affected)  H.    Create a user-defined system stored procedureThis example creates a procedure to display all the tables and their corresponding indexes with a table name beginning with the string emp. If not specified, this procedure returns all tables (and indexes) with a table name beginning with sys.IF EXISTS (SELECT name FROM sysobjectsWHERE name = 'sp_showindexes' AND type = 'P')
DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
@@TABLE varchar(30) = 'sys%'
AS
SELECT o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO
USE pubs
EXEC sp_showindexes 'emp%'
GO
  Here is the result set:TABLE_NAME       INDEX_NAME       INDEX_ID ---------------- ---------------- ----------------emp_pay          employeeID_ind   1employee         employee_ind     1employee         PK_emp_id        2  (3 row(s) affected)  I.    Use deferred name resolutionThis example shows four procedures and the various ways that deferred name resolution can be used. Each stored procedure is created, although the table or column referenced does not exist at compile time.IF EXISTS (SELECT name FROM sysobjectsWHERE name = 'proc1' AND type = 'P')
DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
SELECT *
FROM does_not_exist
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'P' AND o.name = 'proc1'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc2' AND type = 'P')DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
DECLARE @middle_init char(1)
SET @middle_init = NULL
SELECT au_id, middle_initial = @middle_init
FROM authors
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'P' and o.name = 'proc2'

调试易

存储过程，给个例子

解决方案 »